# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
moment  = require 'moment'
fs      = require 'fs'
mkdirp  = require 'mkdirp'
path    = require 'path'
router = express.Router()
module.exports = router


router.all '/', (req, res, next) ->
    res.locals.stat_title = '交易按类型汇总'

    where_clause = """(
        trinfo::json#>>'{"code"}' like $1 or
        trinfo::json#>>'{"name"}' like $1
    )"""

    today = moment().format('YYYY-MM-DD')

    # 如果没有提供日期，则仅查询当日
    start_date = req.body.start_date or req.query.start_date or today
    end_date = req.body.end_date or req.query.end_date or today

    where_clause += " and sys_time::date >= '#{start_date}'"
    res.locals.start_date = start_date
    where_clause += " and sys_time::date <= '#{end_date}'"
    res.locals.end_date = end_date

    table_name = 'pay_trlist_hist'
    if start_date is today and end_date is today
        table_name = 'pay_trlist'

    # 通过成功或失败状态过滤
    res.locals.succed = req.body.succed or req.query.succed or 'true'
    if res.locals.succed isnt 'none'
        if res.locals.succed is 'true'
            where_clause += """ and status = 0 and
            case
            when chnl in (1,4) then
                ( o_pkt is not null and
                json_extract_path_text(o_pkt, '39') = '00')
            when chnl = 3 then
                ( n_pkt is not null and
                json_extract_path_text(n_pkt, 'respCode') = '00')
            end
            """
        else
            where_clause += """ and (status <> 0 or
            case
            when chnl in (1,4) then
                ( o_pkt is null or
                json_extract_path_text(o_pkt, '39') <> '00')
            when chnl = 3 then
                ( n_pkt is null or
                json_extract_path_text(n_pkt, 'respCode') <> '00')
            end)
            """

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询所有清算类交易汇总
    await client.query \
        "select chnl,
            json_extract_path_text(trinfo, 'code') as trcode,
            json_extract_path_text(trinfo, 'name') as trname,
            count(*) as total_cnt,
            round(sum(coalesce(fee, 0.0)), 2) as total_fee,
            case
            when chnl in (1,4) then
                round(sum(cast(
                    json_extract_path_text(i_pkt,'4')
                as decimal)) / 100, 2)
            when chnl = 3 then
                round(sum(cast(
                    json_extract_path_text(i_pkt,'txnAmt')
                as decimal)) / 100, 2)
            end as total_amt
            from #{table_name}
            where #{where_clause} and incoming = false and
                json_extract_path_text(trinfo, 'settl') = 'true'
            group by chnl, trcode, trname
            order by chnl, trcode", [
                "%#{res.locals.keyword}%"
            ], defer err, result
    if err then done(client); return next(err)
    done()

    res.locals.records = result.rows
    res.locals.total = res.locals.records.length
    res.render 'trstat/trcodes'
